psqlいらず？Redshift用CLIツールのAmazon Redshift RSQLを試してみた
データ・アナリティクス事業本部の森脇です。
先日、Redshift用のコマンドラインインターフェースであるAmazon Redshift RSQLされました。
従来RedshiftのCLIツールと言えばpsqlが定番でしたが、とうとうRedshift用のツールがリリースされました！
発表によると
Amazon Redshift RSQL は、Amazon Redshift 固有の機能の追加セットを備えた PostgreSQL psql コマンドラインツールの機能をサポートしています。
とのことなので、psqlの機能が使えつつ拡張でRedshift固有機能にも対応しているようです。
これであればpsqlからの乗り換えも容易そうです。
早速Amazon Linux2にインストールして試してみました。
インストール
ユーザーズガイドを参照すると、まずはODBC関連ツールをインストール/設定する必要があるようです。
(つまり、RSQLはODBC接続でRedshiftと通信を行うようです)
ODBC関連ツールのインストール
1. unixODBCのインストール
sudo yum install unixODBC
2. Redshift用のODBCドライバーをダウンロード
上記ドキュメント内のリンクを選択し、ドライバーをダウンロードします。
今回は64bit版を選択しました。
3. ダウンロードしたODBCドライバーをインストール
sudo yum -y --nogpgcheck localinstall AmazonRedshiftODBC-64-bit-1.4.34.1000-1.x86_64.rpm
4. ~/.bash_profileに以下を追記
export ODBCINI=~/.odbc.ini export ODBCSYSINI=/opt/amazon/redshiftodbc/Setup export AMAZONREDSHIFTODBCINI=/opt/amazon/redshiftodbc/lib/64/amazon.redshiftodbc.ini
5. 追記した設定の読みこみ
source ~/.bash_profile
RSQLのインストール
ODBC関連ツールがインストールできたので、本体のRSQLをインストールしていきます。
1. RSQLをダウンロード
対象OSがAmazon Linux2なので、rpmを選択しました。
2. インストール
sudo rpm -i AmazonRedshiftRsql-1.0.1-1.x86_64.rpm
which コマンドでrsqlがインストールされたことを確認できました。
which rsql /usr/bin/rsql
動作確認
まずはヘルプを確認してみます。
rsql is the Amazon Redshift interactive terminal.
Usage:
rsql [OPTION]... [DBNAME [USERNAME]]
General options:
-c, --command=COMMAND run only single command (SQL or internal) and exit
-d, --dbname=DBNAME database name to connect to (default: "dev")
-f, --file=FILENAME execute commands from file, then exit
-l, --list list available databases, then exit
-v, --set=, --variable=NAME=VALUE
set rsql variable NAME to VALUE
(e.g., -v ON_ERROR_STOP=1)
-V, --version output version information, then exit
-X, --no-psqlrc do not read startup file (~/.psqlrc)
-1 ("one"), --single-transaction
execute as a single transaction (if non-interactive)
-?, --help[=options] show this help, then exit
--help=commands list backslash commands, then exit
--help=variables list special variables, then exit
Input and output options:
-a, --echo-all echo all input from script
-b, --echo-errors echo failed commands
-e, --echo-queries echo commands sent to server
-E, --echo-hidden display queries that internal commands generate
-L, --log-file=FILENAME send session log to file
-n, --no-readline disable enhanced command line editing (readline)
-o, --output=FILENAME send query results to file (or |pipe)
-q, --quiet run quietly (no messages, only query output)
-s, --single-step single-step mode (confirm each query)
-S, --single-line single-line mode (end of line terminates SQL command)
Output format options:
-A, --no-align unaligned table output mode
--csv CSV (Comma-Separated Values) table output mode
-F, --field-separator=STRING
field separator for unaligned output (default: "|")
-H, --html HTML table output mode
-P, --pset=VAR[=ARG] set printing option VAR to ARG (see \pset command)
-R, --record-separator=STRING
record separator for unaligned output (default: newline)
-t, --tuples-only print rows only
-T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border)
-x, --expanded turn on expanded table output
-z, --field-separator-zero
set field separator for unaligned output to zero byte
-0, --record-separator-zero
set record separator for unaligned output to zero byte
Connection options:
-h, --host=HOSTNAME database server host (default: "")
-p, --port=PORT database server port (default: "5439")
-U, --username=USERNAME database user name (default: "jmc-dev")
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
For more information, type "\?" (for internal commands) from within rsql, or consult the rsql section in the Amazon Redshift
documentation.
接続系のオプションはpsqlと互換性がありそうです。
接続
$ rsql -h sample-redshift-cluster.cjhysixos9pk.ap-northeast-1.redshift.amazonaws.com -U root -d dev Password for user root: DSN-less Connected DBMS Name: Amazon Redshift Driver Name: Amazon Redshift ODBC Driver Driver Version: 1.4.34.1000 Rsql Version: 1.0.1 Redshift Version: 1.0.29551 Type "help" for help. (sample-redshift-cluster) root@dev=#
psqlと同じ使い勝手で接続することができました。
ちなみにPGPASSWORDは使えませんでした。
$ PGPASSWORD='password' rsql -h sample-redshift-cluster.cjhysixos9pk.ap-northeast-1.redshift.amazonaws.com -U root -d dev Password for user root:
psqlの場合、-pオプションで5439を設定するのが地味に面倒くさかったですが、rsqlはデフォルト値が5439になっているので指定の必要はありませんでした。
また、バックエンドがODBCである強みを生かし、DSNを利用した様々な接続方法にも対応しています。
(以降は、~/.odbc.iniに設定して試しました)
パスワード認証
先程と同様の、ユーザー，パスワードによる認証です。UIDにユーザー名を、PWDにパスワードを指定します。
ODBCの流儀ですね！
[test-password] Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so Host=sample-redshift-cluster.cjhysixos9pk.ap-northeast-1.redshift.amazonaws.com Port=5439 Database=dev UID=root PWD=password
※ Driverに設定するパスはインストール先のOSによって異なります
-DオプションでDSN名を指定し、接続します。
rsql -D test-password
IAMプロファイル認証
プロファイル名を指定して、IAMにて認証を行うことが可能です。
この方法で認証をする場合、対象のIAMプロファイルにGetClusterCredentials権限が必要です。
設定上は、IAMに1を指定します。
※ここでいう「1」は、真偽値(true/false)のtrueという意味です。
また、DbUserにRedshiftのユーザー名を指定し、Profileに利用するプロファイル名を指定します。
[test-iam-profile] Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so Host=sample-redshift-cluster.cjhysixos9pk.ap-northeast-1.redshift.amazonaws.com Port=5439 Database=dev DbUser=root IAM=1 Profile=example
rsql -D test-iam-profile
インスタンスプロファイル認証
EC2のインスタンスプロファイルを利用して認証することも可能です。
この場合、Instanceprofileに1を指定します。
※IAMプロファイル認証と同様に、EC2に紐付けるIAMにはGetClusterCredentials権限が必要です。
[test-iam-instanceprofile] Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so Host=sample-redshift-cluster.cjhysixos9pk.ap-northeast-1.redshift.amazonaws.com Port=5439 Database=dev DbUser=root IAM=1 Instanceprofile=1
rsql -D test-iam-instanceprofile
その他
この他にもシングルサインオンによる認証もサポートされているようでした。
SQLの実行
psqlと同じ使い勝手で利用が可能です。
SELECT
(sample-redshift-cluster) root@dev=> select * from public.table_a; id | name | age | weight | married | registration_date | create_date ----+----------+-----+--------+---------+-------------------+--------------------- 1 | name-a | 49 | 45.2 | 1 | 2020-06-13 | 2021-03-09 10:42:35 2 | name-b | 36 | 76.5 | 1 | 2020-02-17 | 2021-03-09 10:42:35 3 | name-c | 34 | 50.6 | 0 | 2020-10-29 | 2021-03-09 10:42:35 4 | name-d | 23 | 61.7 | 1 | 2020-06-02 | 2021-03-09 10:42:35 5 | name-e | 21 | 35.3 | 1 | 2020-02-12 | 2021-03-09 10:42:35 (5 rows)
INSERT
(sample-redshift-cluster) root@dev=> insert into public.table_a values (10, 'name-new', 10, 30, 0, '2020-01-01', '2021-09-16 00:00:00'); 1 rows affected
DELETE
(sample-redshift-cluster) root@dev=> delete public.table_a; 10 rows affected
COPY
(sample-redshift-cluster) root@dev=> COPY public.table_a FROM 's3://foo-bucket/sample.csv' IAM_ROLE 'arn:aws:iam::123456789:role/test-role' FORMAT AS CS V IGNOREHEADER 1; INFO: Load into table 'table_a' completed, 5 record(s) loaded successfully.
コマンド
psqlで用意されているコマンドもそのまま使えます。
テーブル一覧表示(\d)
(sample-redshift-cluster) root@dev=> \d
List of relations
schema | name | type | owner
--------+----------------------+-------+----------
public | table_a | table | root
public | table_b | table | root
public | table_c | table | root
public | table_b | table | root
(4 rows)
テーブルDDL表示(\d TABLE_NAME)
分散キーや圧縮エンコードの情報も表示されます。
(sample-redshift-cluster) root@dev=> \d public.table_a
Table "public.table_a"
Column | Type | Collation | Nullable | Default Value | Encoding | DistKey | SortKey
-------------------+-----------------------------+----------------+----------+---------------+----------+---------+---------
id | smallint | | YES | | none | 0 | 1
name | character varying(8) | case_sensitive | YES | | lzo | 0 | 0
age | smallint | | YES | | az64 | 0 | 0
weight | double precision | | YES | | none | 0 | 0
married | boolean | | YES | | none | 0 | 0
registration_date | date | | YES | | az64 | 0 | 0
create_date | timestamp without time zone | | YES | | az64 | 0 | 0
Diststyle: ALL
Backup: YES
Created: 2021-04-20 06:44:11.012394
拡張ディスプレイ(\x)
(sample-redshift-cluster) root@dev=> \x Expanded display is on. (sample-redshift-cluster) root@dev=> select * from public.table_b; -[ RECORD 1 ]-----+-------------------- id | 1 name | user_1 age | 49 weight | 45.2 married | 1 registration_date | 2020-06-13 create_date | 2021-03-09 10:42:35 -[ RECORD 2 ]-----+-------------------- id | 2 name | user_2 age | 36 weight | 76.5 married | 1 registration_date | 2020-02-17 create_date | 2021-03-09 10:42:35 -[ RECORD 3 ]-----+-------------------- id | 3 name | user_3 age | 34 weight | 50.6 married | 0 registration_date | 2020-10-29 create_date | 2021-03-09 10:42:35 (sample-redshift-cluster) root@dev=> \x Expanded display is off.
まとめ
rsqlを使うためには、ODBC関連ツール(unixODBCやRedshiftのODBCドライバー)を事前にインストールする必要があり、psqlよりも導入に手間がかかりそうでした。
その一方で、IAMプロファイル、EC2のインスタンスプロファイルによる認証が利用可能であり、よりセキュアにRedshiftに接続できました。
また、psqlの操作感で利用可能であり、コマンドも同じように利用できるため、乗り換えはスムーズに行えそうです。
個人的に、\dコマンドによるテーブルDDL表示が分散スタイル，圧縮エンコードも表示されて非常に便利だなと感じました。
![[アップデート] Amazon Redshiftのマルチデータウェアハウス書き込み機能が一般提供開始されたので試してみた](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-63f1274931b942e9a92e601c1127ad73/cfe87ec6d62fa2fc3c474ed4cb2f6c2e/amazon-redshift)
